package com.yuke.cloud.common.util.excel;

/*
 * <p>Company: Copyright (c) 2018. yuke All Rights Reserved. </br> </p>
 * <p>Title：yuke-mall </br> </p>
 * <p>com.yuke.cloud.service.omc.utils Description TODO </br> </p>
 * @Author wg
 * @Date   2020/5/2 15:47
 */

import com.google.common.base.Strings;
import lombok.Data;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @作者 yan
 * @创建日期
 * @版本 V1.0
 * @描述 Excel 导出通用工具类
 */
@Data
public class ExcelOutUtil {
    //根目录 +/目录/目录
    private static String linuxBasePath = File.separator+"tmp"+File.separator+"paper"+File.separator;
    private static String winBasePath = "D:/paper/";
    private static String macBasePath = "/Users/Administrator/Desktop/";

    //文件名称
    private String fileName;
    //表头
    private String title;
    //各个列的表头
    private String[] heardList;
    //各个列的元素key值
    private String[] heardKey;
    //需要填充的数据信息
    private List<Map> data;
    //字体大小
    private int fontSize = 14;
    //行高
    private int rowHeight = 30;
    //列宽
    private int columWidth = 200;
    //工作表
    private String sheetName = "sheet1";


    /**
     * 开始导出数据信息
     */
     public String exportExport(HttpServletRequest request, HttpServletResponse response, int page, int downNum,String mergePaperPath) throws IOException {

        //检查参数配置信息
        checkConfig();
        //创建工作簿
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建工作表
        HSSFSheet wbSheet = wb.createSheet(this.sheetName);
        //设置默认行宽
        wbSheet.setDefaultColumnWidth(20);

        // 标题样式（加粗，垂直居中）
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        HSSFFont fontStyle = wb.createFont();
        //fontStyle.setBoldweight(Font.BOLDWEIGHT_BOLD);
        fontStyle.setBold(true);   //加粗
        fontStyle.setFontHeightInPoints((short) 16);  //设置标题字体大小
        cellStyle.setFont(fontStyle);

        //在第0行创建rows  (表标题)
        HSSFRow title = wbSheet.createRow((int) 0);
        title.setHeightInPoints(30);//行高
        HSSFCell cellValue = title.createCell(0);
        cellValue.setCellValue(this.title);
        cellValue.setCellStyle(cellStyle);
        wbSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (this.heardList.length - 1)));
        //设置表头样式，表头居中
        HSSFCellStyle style = wb.createCellStyle();
        //设置单元格样式
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) this.fontSize);
        style.setFont(font);
        //在第1行创建rows
        HSSFRow row = wbSheet.createRow((int) 1);
        //设置列头元素
        HSSFCell cellHead = null;
        for (int i = 0; i < heardList.length; i++) {
            cellHead = row.createCell(i);
            cellHead.setCellValue(heardList[i]);
            cellHead.setCellStyle(style);
        }

        //设置每格数据的样式 （字体红色）
        HSSFCellStyle cellParamStyle = wb.createCellStyle();
        HSSFFont ParamFontStyle = wb.createFont();
        cellParamStyle.setAlignment(HorizontalAlignment.CENTER);
        cellParamStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        ParamFontStyle.setColor(HSSFColor.DARK_RED.index);   //设置字体颜色 (红色)
        ParamFontStyle.setFontHeightInPoints((short) this.fontSize);
        cellParamStyle.setFont(ParamFontStyle);
        //设置每格数据的样式2（字体蓝色）
        HSSFCellStyle cellParamStyle2 = wb.createCellStyle();
        cellParamStyle2.setAlignment(HorizontalAlignment.CENTER);
        cellParamStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont ParamFontStyle2 = wb.createFont();
        ParamFontStyle2.setColor(HSSFColor.BLUE.index);   //设置字体颜色 (蓝色)
        ParamFontStyle2.setFontHeightInPoints((short) this.fontSize);
        cellParamStyle2.setFont(ParamFontStyle2);
        //开始写入实体数据信息
        int a = 2;
        for (int i = 0; i < data.size(); i++) {
            HSSFRow roww = wbSheet.createRow((int) a);
            Map map = data.get(i);
            HSSFCell cell = null;
            for (int j = 0; j < heardKey.length; j++) {
                cell = roww.createCell(j);
                cell.setCellStyle(style);
                Object valueObject = map.get(heardKey[j]);
                String value = null;
                if (valueObject == null) {
                    valueObject = "";
                }
                if (valueObject instanceof String) {
                    //取出的数据是字符串直接赋值
                    value = (String) map.get(heardKey[j]);
                } else if (valueObject instanceof Integer) {
                    //取出的数据是Integer
                    value = String.valueOf(((Integer) (valueObject)).floatValue());
                } else if (valueObject instanceof BigDecimal) {
                    //取出的数据是BigDecimal
                    value = String.valueOf(((BigDecimal) (valueObject)).floatValue());
                } else {
                    value = valueObject.toString();
                }
                //设置单个单元格的字体颜色
                if (heardKey[j].equals("ddNum") || heardKey[j].equals("sjNum")) {
                    if ((Long) map.get("ddNum") != null) {
                        if ((Long) map.get("sjNum") == null) {
                            cell.setCellStyle(cellParamStyle);
                        } else if ((Long) map.get("ddNum") != (Long) map.get("sjNum")) {
                            if ((Long) map.get("ddNum") > (Long) map.get("sjNum")) {
                                cell.setCellStyle(cellParamStyle);
                            }
                            if ((Long) map.get("ddNum") < (Long) map.get("sjNum")) {
                                cell.setCellStyle(cellParamStyle2);
                            }
                        } else {
                            cell.setCellStyle(style);
                        }
                    }
                }
                cell.setCellValue(Strings.isNullOrEmpty(value) ? "" : value);
            }
            a++;
        }

         //拿到目录地址
         String s = mergePaperPath+"page"+page + ".xls";
         File file = new File(s);

         if (!file.exists()){
             file.setWritable(true, false);
             file.getParentFile().mkdirs();
             file.createNewFile();
         }

         wb.write(file);
         return s;



    }

    public byte[] downLoadBrowser (HttpServletResponse response, HSSFWorkbook wb,String fileName) throws IOException {
        try{
            //设置Http响应头告诉浏览器下载这个附件
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;Filename=" + fileName);
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.close();
            return wb.getBytes();
        }catch (Exception ex){
            ex.printStackTrace();
            throw new IOException("导出Excel出现严重异常，异常信息：" + ex.getMessage());
        }
    }

    public byte[] downLoad (HttpServletResponse response, HSSFWorkbook wb, int page) throws IOException {
//        导出数据
        try {
            String s="D:\\订单信息表"+page+".xls";
            FileOutputStream outputStream = new FileOutputStream(s);
            wb.write(outputStream);
            outputStream.close();
            return wb.getBytes();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现严重异常，异常信息：" + ex.getMessage());
        }
    }

    /**
     * 检查数据配置问题
     *
     * @throws IOException 抛出数据异常类
     */
    protected void checkConfig () throws IOException {
        if (heardKey == null || heardList.length == 0) {
            throw new IOException("列名数组不能为空或者为NULL");
        }

        if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {
            throw new IOException("字体、宽度或者高度不能为负值");
        }

        if (Strings.isNullOrEmpty(sheetName)) {
            throw new IOException("工作表表名不能为NULL");
        }
    }

    public void toZip(HttpServletResponse response,HSSFWorkbook wb,int page,String fileName) throws IOException {
        System.out.println("toZip");

        try(        //response 输出流
                    ServletOutputStream out = response.getOutputStream();
                    //压缩输出流
                    ZipOutputStream zipOutputStream = new ZipOutputStream(out);
        ) {
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName+".zip");
            //创建压缩文件
            ZipEntry z = new ZipEntry(page + ".xls");
            zipOutputStream.putNextEntry(z);
            //写入一个压缩文件
            wb.write(zipOutputStream);
            System.out.println("wb");
            //zipOutputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 帮助下载文件
     * @param request
     * @param response
     * @param path
     * @return
     */
    public static byte[] helpdown(HttpServletRequest request, HttpServletResponse response, String path) {
        response.setCharacterEncoding(request.getCharacterEncoding());
        response.setContentType("application/octet-stream");
        FileInputStream fis = null;
        byte[] bytes = null;
        try {
            File file = new File(path);
            fis = new FileInputStream(file);
            response.setHeader("Content-Disposition", "attachment:filename="+file.getName());
            System.out.println(file.getName());
            IOUtils.copy(fis,response.getOutputStream());
//            bytes = fileConvertToByteArray(file);
            response.flushBuffer();
            return bytes;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            return bytes;
        }
    }

    /**
     * 根据时间创立文件夹
     * @param date
     * @return 返回的是 文件夹目录地址
     */
    public static String getMergePaperPath(Date date){
        SimpleDateFormat format= new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        String s = format.format(date);
        //
        String realBasePath = getRealBasePath();
        //跟 目录 + 合成 + 时间戳 + /
        String res = realBasePath + "merge" + File.separator + s +File.separator;
        return res;
    }


    /**
     * 获取目录
     * @return
     */
    public static String getRealBasePath() {
        String os =System.getProperty("os.name");
        System.out.println("当前操作系统名称;"+os);
        if (os.toLowerCase().startsWith("win")){
            return winBasePath;
        }else if(os.toLowerCase().startsWith("Mac OS")){
            return macBasePath;
        }
        else if (os.toLowerCase().indexOf("linux") >= 0){
            return linuxBasePath;
        }
        else{
            return winBasePath;
        }
    }

}
